<?php

namespace app\index\controller;
use think\Db as perceiveDB;

class TechnologySituationPerceive extends Common{
    public $datas;

    /**
     * 标准管理
     */
    public function getStandardManage(){
        try {
            $sql = "SELECT a.*,IFNULL(b.dayCount,0) dayCount FROM (SELECT resourceCode,COUNT(resourceCode) count FROM resource_collection GROUP BY resourceCode) a "
                    ."LEFT JOIN (SELECT resourceCode,COUNT(resourceCode) dayCount FROM resource_collection "
                    ."WHERE LEFT(create_time,10) = (SELECT LEFT(create_time,10) FROM resource_collection ORDER BY create_time desc limit 1 )) b "
                    ."ON a.resourceCode = b.resourceCode";
            $res = perceiveDB::query($sql);
            $sql = "SELECT LEFT(create_time,10) recentCollectionTime FROM resource_collection ORDER BY create_time desc limit 1";
            $recentCollectionTime = perceiveDB::query($sql);

            $returnData = [];
            $returnData['standardManageTime'] = $res;
            $returnData['recentCollectionTime'] = $recentCollectionTime[0]['recentCollectionTime'];
            if(empty($returnData)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $returnData);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 近期成果入库统计
     */
    public function getRecentlyResultEntrant(){
        try {
            //成果数量
            $sql = "SELECT resourceCode,COUNT(resourceCode) count FROM resource_collection GROUP BY resourceCode";
            $resultOther = perceiveDB::query($sql);

            //级别数量和转让次数
            $sql = "SELECT resourceCode,level,count(level) times FROM resource_collection "
                    ."WHERE resourceCode != 'P' GROUP BY resourceCode,level ORDER BY resourceCode";
            $levelResult = perceiveDB::query($sql);
            $sql = "SELECT resourceCode,sum(transferCount) transferCount FROM resource_collection "
                ."WHERE resourceCode = 'P' GROUP BY resourceCode";
            $transferResult = perceiveDB::query($sql);
            $levelTralsferResult = array_merge($levelResult,$transferResult);


            $returnArray = [];
            $returnArray['resultCount'] = $resultOther;
            $returnArray['levelCount'] = $levelTralsferResult;

            if(empty($resultStandard) && empty($levelTralsferResult)){
                $this->returnMsg(1, '暂无数据！');
            }
            $this->returnMsg(1, '获取数据成功！', $returnArray);

        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 期刊论文深度画像-概览
     */
    public function getPaperPortraitOverview(){
        $this->datas = $this->params;

        $condition = "";
        if(empty($this->datas['resourceCode'])){
            $condition .= " and resourceCode in ('J','I','E','U') ";
        }else{
            if($this->datas['resourceCode'] != 'all'){
                $condition .= " and resourceCode = '".$this->datas['resourceCode']."' ";
            }
        }
        if(!empty($this->datas['yearFrom']) && !empty($this->datas['yearTo'])){
            $condition .= " AND year >= '".$this->datas['yearFrom']."' AND year <= '".$this->datas['yearTo']."' ";
        }
        if(!empty($this->datas['secondaryCollege'])){
            $condition .= " AND secondaryCollege = '".$this->datas['secondaryCollege']."' ";
        }
        try {
            //$sql = "SELECT year,COUNT(year) count FROM resource_collection WHERE 1=1 ".$condition
            //        ." GROUP BY year ORDER BY year";
            $sql = "SELECT resourceCode,year,COUNT(year) count FROM resource_collection WHERE 1=1 ".$condition
                    ." and year is not null and year != 0 and LENGTH(year) = 4 "
                    ." GROUP BY resourceCode,year ORDER BY resourceCode,year";
            $res = perceiveDB::query($sql);

            //$sql = "SELECT subject,COUNT(subject) count FROM resource_collection WHERE  ".$condition
            //        ." GROUP BY subject";
            $sql = "SELECT resourceCode,subject,COUNT(subject) count FROM resource_collection WHERE 1=1 ".$condition
                    ." and subject <> '' and subject is not null  "
                    ." GROUP BY subject,resourceCode ORDER BY resourceCode";
            $pieRes = perceiveDB::query($sql);

            $sql = "SELECT resourceCode,category,COUNT(category) categoryCount FROM resource_collection "
                ."WHERE resourceCode = 'O' and category is not null and category != '' ".$condition
                ."GROUP BY category ORDER BY categoryCount desc ";
            $projectRes = perceiveDB::query($sql);

            $returnRes = [];
            $returnRes['lineRes'] = $res;
            $returnRes['pieRes'] = $pieRes;
            $returnRes['projectRes'] = $projectRes;

            if(empty($res) && empty($pieRes) && empty($projectRes)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $returnRes);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 期刊论文深度画像-明细
     */
    public function getPaperPortraitDetail(){
        $this->datas = $this->params;

        $condition = "";
        if(empty($this->datas['resourceCode'])){
            $condition .= " resourceCode in ('J','I','E','U') ";
        }else{
            $condition .= " resourceCode = '".$this->datas['resourceCode']."' ";
        }
        if(!empty($this->datas['yearFrom']) && !empty($this->datas['yearTo'])){
            $condition .= " AND year >= '".$this->datas['yearFrom']."' AND year <= '".$this->datas['yearTo']."' ";
        }
        if(!empty($this->datas['secondaryCollege'])){
            $condition .= " AND secondaryCollege = '".$this->datas['secondaryCollege']."' ";
        }
        if(!empty($this->datas['level'])){
            $condition .= " AND level = '".$this->datas['level']."' ";
        }
        if(!empty($this->datas['creator'])){
            $condition .= " AND creator like '%".$this->datas['creator']."%' ";
        }

        try {
            $sql = "SELECT year,COUNT(year) count FROM resource_collection WHERE  ".$condition
                ." and year is not null and year != 0 and LENGTH(year) = 4 "
                ." GROUP BY year ORDER BY year";
            $res = perceiveDB::query($sql);

            $sql = "SELECT subject,COUNT(subject) count FROM resource_collection WHERE  ".$condition
                ." GROUP BY subject";
            $pieRes = perceiveDB::query($sql);


            $pageNo = 1;
            $pageSize = 10;

            if(!empty($this->datas['pageNo'])){
                $pageNo = $this->datas['pageNo'];
            }

            if(!empty($this->datas['pageSize'])){
                $pageSize = $this->datas['pageSize'];
            }

            $pageNo = ($pageNo - 1) * $pageSize;


            $sql = "SELECT * FROM resource_collection WHERE  ".$condition
                    ." limit ".$pageNo.",".$pageSize;
            $pageRes = perceiveDB::query($sql);
            $sql = "SELECT COUNT(1) num FROM resource_collection WHERE  ".$condition;
            $countRes = perceiveDB::query($sql);


            $returnRes = [];
            $returnRes['lineRes'] = $res;
            $returnRes['pieRes'] = $pieRes;
            $returnRes['tableData'] = $pageRes;
            $returnRes['tableTotal'] = $countRes[0]['num'];

            if(empty($res) && empty($pieRes) && empty($pageRes)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $returnRes);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 知识产权深度画像
     */
    public function getIntellectualPropertyPortrait(){
        $this->datas = $this->params;

        $condition = "";
        if(!empty($this->datas['patentType'])){
            $condition .= " AND patentType = '".$this->datas['patentType']."' ";
        }
        if(!empty($this->datas['yearFrom']) && !empty($this->datas['yearTo'])){
            $condition .= " AND year >= '".$this->datas['yearFrom']."' AND year <= '".$this->datas['yearTo']."' ";
        }
        if(!empty($this->datas['secondaryCollege'])){
            $condition .= " AND secondaryCollege = '".$this->datas['secondaryCollege']."' ";
        }
        if(!empty($this->datas['creator'])){
            //$condition .= " AND creator like '%".$this->datas['creator']."%' ";
            $condition .= " AND creatorStandard like '%".$this->datas['creator']."%' ";
        }
        try {
            $sql = "SELECT year,COUNT(year) count FROM resource_collection WHERE resourceCode ='P' ".$condition
                ." and year is not null and year != 0 and LENGTH(year) = 4 "
                ." GROUP BY year ORDER BY year";
            $res = perceiveDB::query($sql);

            $sql = "SELECT domain,COUNT(domain) count FROM resource_collection WHERE resourceCode ='P' ".$condition
                ." and domain is not null GROUP BY domain";
            $pieRes = perceiveDB::query($sql);


            $pageNo = 1;
            $pageSize = 10;

            if(!empty($this->datas['pageNo'])){
                $pageNo = $this->datas['pageNo'];
            }

            if(!empty($this->datas['pageSize'])){
                $pageSize = $this->datas['pageSize'];
            }

            $pageNo = ($pageNo - 1) * $pageSize;


            $sql = "SELECT title,patentName,creatorAll,allInventors,patentCategory,type,applicationDate,authorizationTime,legalState,applicationNo,patentNo,publicationDate,tag,url FROM resource_collection WHERE resourceCode ='P' ".$condition
                ." limit ".$pageNo.",".$pageSize;
            $pageRes = perceiveDB::query($sql);
            $sql = "SELECT COUNT(1) num FROM resource_collection WHERE resourceCode ='P' ".$condition;
            $countRes = perceiveDB::query($sql);


            $returnRes = [];
            $returnRes['lineRes'] = $res;
            $returnRes['pieRes'] = $pieRes;
            $returnRes['tableData'] = $pageRes;
            $returnRes['tableTotal'] = $countRes[0]['num'];

            if(empty($res) && empty($pieRes) && empty($pageRes)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $returnRes);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 成果奖励深度画像
     */
    public function getResultsAwardPortrait(){
        $this->datas = $this->params;

        $condition = "";
        if(!empty($this->datas['yearFrom']) && !empty($this->datas['yearTo'])){
            $condition .= " AND year >= '".$this->datas['yearFrom']."' AND year <= '".$this->datas['yearTo']."' ";
        }
        if(!empty($this->datas['secondaryCollege'])){
            $condition .= " AND secondaryCollege = '".$this->datas['secondaryCollege']."' ";
        }
        if(!empty($this->datas['creator'])){
            //$condition .= " AND creator like '%".$this->datas['creator']."%' ";
            $condition .= " AND creatorStandard like '%".$this->datas['creator']."%' ";
        }
        if(!empty($this->datas['level'])){
            $condition .= " AND level = '".$this->datas['level']."' ";
        }
        try {
            $sql = "SELECT year,COUNT(year) count FROM resource_collection WHERE resourceCode ='A' ".$condition
                ." and year is not null and year != 0 and LENGTH(year) = 4 "
                ." GROUP BY year ORDER BY year";
            $res = perceiveDB::query($sql);

            $sql = "SELECT subjectClassification subject,COUNT(subjectClassification) count FROM resource_collection WHERE "
                    ." resourceCode ='A' and subjectClassification <> '' and subjectClassification is not null "
                    .$condition
                    ." GROUP BY subjectClassification ORDER BY count desc";
            $pieRes = perceiveDB::query($sql);


            $pageNo = 1;
            $pageSize = 10;

            if(!empty($this->datas['pageNo'])){
                $pageNo = $this->datas['pageNo'];
            }

            if(!empty($this->datas['pageSize'])){
                $pageSize = $this->datas['pageSize'];
            }

            $pageNo = ($pageNo - 1) * $pageSize;


            $sql = "SELECT title,allCompletedPeople,awardType,awardWinningYear,awardWinningLevel,achievementName,tag,url,create_time createTime FROM resource_collection WHERE resourceCode ='A' ".$condition
                ." limit ".$pageNo.",".$pageSize;
            $pageRes = perceiveDB::query($sql);
            $sql = "SELECT COUNT(1) num FROM resource_collection WHERE resourceCode ='A' ".$condition;
            $countRes = perceiveDB::query($sql);


            $returnRes = [];
            $returnRes['lineRes'] = $res;
            $returnRes['pieRes'] = $pieRes;
            $returnRes['tableData'] = $pageRes;
            $returnRes['tableTotal'] = $countRes[0]['num'];

            if(empty($res) && empty($pieRes) && empty($pageRes)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $returnRes);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }

    /**
     * 科研项目深度画像
     */
    public function getResearchProjectPortrait(){
        $this->datas = $this->params;

        $condition = "";
        if(!empty($this->datas['yearFrom']) && !empty($this->datas['yearTo'])){
            $condition .= " AND year >= '".$this->datas['yearFrom']."' AND year <= '".$this->datas['yearTo']."' ";
        }
        if(!empty($this->datas['secondaryCollege'])){
            $condition .= " AND secondaryCollege = '".$this->datas['secondaryCollege']."' ";
        }
        if(!empty($this->datas['creator'])){
            //$condition .= " AND creator like '%".$this->datas['creator']."%' ";
            $condition .= " AND creatorStandard like '%".$this->datas['creator']."%' ";
        }
        if(!empty($this->datas['projectLevel'])){
            $condition .= " AND projectLevel = '".$this->datas['projectLevel']."' ";
        }
        if(!empty($this->datas['projectFund'])){
            $condition .= " AND projectFund = '".$this->datas['projectFund']."' ";
        }
        if(!empty($this->datas['projectName'])){
            $condition .= " AND projectName like '%".$this->datas['projectName']."%' ";
        }

        try {
            $sql = "SELECT year,COUNT(year) count FROM resource_collection WHERE resourceCode ='O' ".$condition
                ." and year is not null and year != 0 and year <= YEAR(NOW()) "
                ." GROUP BY year ORDER BY year";
            $res = perceiveDB::query($sql);

            /*$sql = "SELECT subjectClassification subject,COUNT(subjectClassification) count FROM resource_collection WHERE "
                ." resourceCode ='O' and subjectClassification <> '' and subjectClassification is not null "
                .$condition
                ." GROUP BY subjectClassification ORDER BY count desc";
            $pieRes = perceiveDB::query($sql);*/
            $sql = "SELECT category,COUNT(category) categoryCount FROM resource_collection "
                ."WHERE resourceCode = 'O' and category is not null and category != '' ".$condition
                ."GROUP BY category ORDER BY categoryCount desc ";
            $pieRes = perceiveDB::query($sql);


            $pageNo = 1;
            $pageSize = 10;

            if(!empty($this->datas['pageNo'])){
                $pageNo = $this->datas['pageNo'];
            }

            if(!empty($this->datas['pageSize'])){
                $pageSize = $this->datas['pageSize'];
            }

            $pageNo = ($pageNo - 1) * $pageSize;

            $sql = "SELECT title,projectName,projectFund,projectLevel,principal,creatorAll,instituteAll,keyword,journal,tag,url,create_time createTime FROM resource_collection WHERE resourceCode ='O' ".$condition
                ." limit ".$pageNo.",".$pageSize;
            $pageRes = perceiveDB::query($sql);
            $sql = "SELECT COUNT(1) num FROM resource_collection WHERE resourceCode ='O' ".$condition;
            $countRes = perceiveDB::query($sql);


            $returnRes = [];
            $returnRes['lineRes'] = $res;
            $returnRes['pieRes'] = $pieRes;
            $returnRes['tableData'] = $pageRes;
            $returnRes['tableTotal'] = $countRes[0]['num'];

            if(empty($res) && empty($pieRes) && empty($pageRes)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $returnRes);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 专著统计
     */
    public function getBookStatistics(){
        $this->datas = $this->params;

        $condition = "";
        if(!empty($this->datas['yearFrom']) && !empty($this->datas['yearTo'])){
            $condition .= " AND year >= '".$this->datas['yearFrom']."' AND year <= '".$this->datas['yearTo']."' ";
        }
        if(!empty($this->datas['secondaryCollege'])){
            $condition .= " AND secondaryCollege = '".$this->datas['secondaryCollege']."' ";
        }
        if(!empty($this->datas['creator'])){
            //$condition .= " AND creator like '%".$this->datas['creator']."%' ";
            $condition .= " AND creatorStandard like '%".$this->datas['creator']."%' ";
        }
        if(!empty($this->datas['level'])){
            $condition .= " AND level = '".$this->datas['level']."' ";
        }


        try {
            $sql = "SELECT year,COUNT(year) count FROM resource_collection WHERE resourceCode ='B' ".$condition
                ." GROUP BY year ORDER BY year";
            $res = perceiveDB::query($sql);

            $pageNo = 1;
            $pageSize = 10;

            if(!empty($this->datas['pageNo'])){
                $pageNo = $this->datas['pageNo'];
            }

            if(!empty($this->datas['pageSize'])){
                $pageSize = $this->datas['pageSize'];
            }

            $pageNo = ($pageNo - 1) * $pageSize;

            $sql = "SELECT title,creator,publisher,isbn,create_time createTime FROM resource_collection WHERE resourceCode ='B' ".$condition
                ." limit ".$pageNo.",".$pageSize;
            $pageRes = perceiveDB::query($sql);
            $sql = "SELECT COUNT(1) num FROM resource_collection WHERE resourceCode ='B' ".$condition;
            $countRes = perceiveDB::query($sql);


            $returnRes = [];
            $returnRes['lineRes'] = $res;
            $returnRes['tableData'] = $pageRes;
            $returnRes['tableTotal'] = $countRes[0]['num'];

            if(empty($res) && empty($pageRes)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $returnRes);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 获取学院列表
     */
    public function getCollegeList(){
        try {
            $sql = "select DISTINCT(secondaryCollege) from resource_collection "
                    ." WHERE secondaryCollege is not null and secondaryCollege != ''";
            $res = perceiveDB::query($sql);

            if(empty($res)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $res);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }

    /**
     * 获取职称列表
     */
    public function getProfessionalTitleList(){
        try {
            $sql = "select DISTINCT(professional_title) professionalTitle from scholar "
                ." WHERE professional_title is not null and professional_title != ''";
            $res = perceiveDB::query($sql);

            if(empty($res)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $res);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }
}